USE [comunion]
GO

/****** Object:  StoredProcedure [dbo].[sp_WRRBalanceInquiry]    Script Date: 06/18/2010 14:34:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- exec sp_PIBalanceInquiry 'RR-090125', '407-00'

CREATE  proc [dbo].[sp_PIBalanceInquiry] 
	@cWRRNo varchar (15),
	@cCompanyID varchar (10)
as

SELECT 
cCompanyID, cTranNo, 
ISNULL(SUM(nGross), 0) AS nGross, 
ISNULL(SUM(nPaid), 0) AS nPaid, 
ISNULL(SUM(nDebit), 0) AS nDebit, 
ISNULL(SUM(nCredit), 0) AS nCredit, 
ISNULL(SUM(nReturns), 0) AS nReturns,
ISNULL(SUM(nGross), 0) - ISNULL(SUM(nPaid), 0) - ISNULL(SUM(nDebit), 0)+
ISNULL(SUM(nCredit), 0) - ISNULL(SUM(nReturns), 0) as nBalance
FROM 
(
(
SELECT A.cCompanyID, A.cTranNo, 
SUM(A.nGross) AS nGross, 0 AS nPaid, 0 AS nDebit, 0 AS nCredit, 0 AS nReturns 
FROM Purchase A 
WHERE A.lCancelled = 0 and A.cTranNo = @cWRRNo and A.cCompanyID = @cCompanyID
GROUP BY A.cCompanyID, A.cTranNo
)
UNION ALL
(
SELECT A.cCompanyID, A.cPINo, 
0 AS nGross, SUM(A.nApplied) AS nPaid, 0 AS nDebit, 0 AS nCredit, 0 AS nReturns
FROM VOUCHER_TRADE A
LEFT OUTER JOIN VOUCHER B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
WHERE B.lCancelled = 0 AND B.lApproved = 1 and B.lCancelled =  0 and A.cPINo = @cWRRNo and A.cCompanyID = @cCompanyID
GROUP BY A.cCompanyID, A.cPINo
)
UNION ALL
(
SELECT A.cCompanyID, A.cPINo, 
0 AS nGross, 0 AS nPaid, SUM(A.nDebit) AS nDebit, 0 AS nCredit, 0 AS nReturns
FROM AP_T A 
LEFT OUTER JOIN AP B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType = 'Debit' and lPurchaseReturn = 0 AND B.lCancelled = 0 and A.cPINo = @cWRRNo and A.cCompanyID = @cCompanyID
GROUP BY A.cCompanyID, A.cPINo
)
UNION ALL
(
SELECT A.cCompanyID, A.cPINo, 
0 AS nGross, 0 AS nPaid, 0 AS nDebit, SUM(A.nCredit) AS nCredit, 0 AS nReturns
FROM AP_T A 
LEFT OUTER JOIN AP B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
WHERE B.cType = 'Credit' AND B.lCancelled = 0 and lPurchaseReturn = 0 and A.cPINo = @cWRRNo and A.cCompanyID = @cCompanyID
GROUP BY A.cCompanyID, A.cPINo
)

UNION ALL
(
SELECT A.cCompanyID, A.cPINo, 
0 AS nGross, 0 AS nPaid, 0 AS nDebit, 0 AS nCredit, SUM(A.nDebit) AS nReturns
FROM AP_T A 
LEFT OUTER JOIN AP B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
WHERE B.lCancelled = 0 and lPurchaseReturn = 1 and A.cPINo = @cWRRNo and A.cCompanyID = @cCompanyID
GROUP BY A.cCompanyID, A.cPINo
)
) A GROUP BY cCompanyID, cTranNo


GO


